<?php

require __DIR__ . '/../../bootstrap.php';
$em = require __DIR__ . '/../../example/em.php';

//$_POST['userId']='22';
//include "../data/mysql.php";

use Service\UserManager;

// Дебаг. Закоментить!
//$_POST['startDate']='12.06.2013';
//$_POST['endDate']='19.06.2013';
//$_POST['type']='amount';


$type = $_POST['type'];
$dateBegin = convertDate($_POST['startDate']);
$dateEnd = convertDate($_POST['endDate']);

$manager = new UserManager($em);
$user = $manager->getUser();

function randcolor()
{
    return 'rgb(' . rand(0, 255) . ',' . rand(0, 255) . ',' . rand(0, 255) . ')';
}

function convertDate($date)
{
    list($day, $month, $year) = explode(".", $date);
    return sprintf("%d-%02d-%02d", $year, $month, $day);
}

//BEGIN KERCH
switch ($type) {
    case 'amount':
        $sql = "SELECT user.name AS label
                 , biling.amount
            FROM
              sim_owner
            INNER JOIN user
            ON sim_owner.user_id = user.id
            INNER JOIN sim
            ON sim_owner.sim_id = sim.id
            INNER JOIN biling
            ON biling.phone = sim.id
            WHERE
              biling.`date` BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
              AND biling.type = 'FILLUP'
              AND sim_owner.state = 1
              AND user.lft >= {$user->getLeft()}
              AND user.rgt <= {$user->getRight()}";
        $result = $em->getConnection()->query($sql)->fetchAll();
        $data = array();
        foreach ($result as $key => $value) {
            @$data[$value['label']] += $value['amount'];
        }
        $result = array();
        $i = 0;
        foreach ($data as $key => $value) {
            $result[$i]['label'] = $key;
            $result[$i]['data'] = $value;
            $result[$i]['color'] = randcolor();
            $i++;
        }
        echo json_encode($result);
        break;

    case 'ORIG':
        $sql = "SELECT user.name AS label
            FROM
              sim_owner
            INNER JOIN user
            ON sim_owner.user_id = user.id
            INNER JOIN sim
            ON sim_owner.sim_id = sim.id
            INNER JOIN biling
            ON biling.phone = sim.id
            WHERE
              biling.date_record BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
              AND biling.type = '$type'
              AND sim_owner.state = 1
              AND user.lft >= {$user->getLeft()}
              AND user.rgt <= {$user->getRight()}";
        //print_r($sql);
        //exit();
        $result = $em->getConnection()->query($sql)->fetchAll();
        $data = '';
        foreach ($result as $key => $value) {
            $data[] = implode(",", $value);
        }
        //print_r($data);
        $data = array_count_values($data);
        $result = array();
        $i = 0;
        foreach ($data as $key => $value) {
            $result[$i]['label'] = $key;
            $result[$i]['data'] = $value;
            $result[$i]['color'] = randcolor();
            $i++;
        }
        echo json_encode($result);
        break;

    case 'TERM':
        $sql = "SELECT user.name AS label
            FROM
              sim_owner
            INNER JOIN user
            ON sim_owner.user_id = user.id
            INNER JOIN sim
            ON sim_owner.sim_id = sim.id
            INNER JOIN biling
            ON biling.phone = sim.id
            WHERE
              biling.date_record BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
              AND biling.type = '$type'
              AND sim_owner.state = 1
              AND user.lft >= {$user->getLeft()}
              AND user.rgt <= {$user->getRight()}";
        $result = $em->getConnection()->query($sql)->fetchAll();
        $data = '';
        foreach ($result as $key => $value) {
            $data[] = implode(",", $value);
        }
        //print_r($data);
        $data = array_count_values($data);
        $result = array();
        $i = 0;
        foreach ($data as $key => $value) {
            $result[$i]['label'] = $key;
            $result[$i]['data'] = $value;
            $result[$i]['color'] = randcolor();
            $i++;
        }
        echo json_encode($result);
        break;
    case 'ORIG_duration':
        $sql = "SELECT user.name AS label
                 , biling.duration
            FROM
              sim_owner
            INNER JOIN user
            ON sim_owner.user_id = user.id
            INNER JOIN sim
            ON sim_owner.sim_id = sim.id
            INNER JOIN biling
            ON biling.phone = sim.id
            WHERE
              biling.date_record BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
              AND biling.type = 'ORIG'
              AND sim_owner.state = 1
              AND user.lft >= {$user->getLeft()}
              AND user.rgt <= {$user->getRight()}";
        $result = $em->getConnection()->query($sql)->fetchAll();
        $data = array();
        foreach ($result as $key => $value) {
            @$data[$value['label']] += $value['duration'];
        }
        $result = array();
        $i = 0;
        foreach ($data as $key => $value) {
            $result[$i]['label'] = $key;
            $result[$i]['data'] = $value;
            $result[$i]['color'] = randcolor();
            $i++;
        }
        //$result['time']='time';
        //print_r($result);
        echo json_encode($result);
        break;
    case 'TERM_duration':
        $sql = "SELECT user.name AS label
                 , biling.duration
            FROM
              sim_owner
            INNER JOIN user
            ON sim_owner.user_id = user.id
            INNER JOIN sim
            ON sim_owner.sim_id = sim.id
            INNER JOIN biling
            ON biling.phone = sim.id
            WHERE
              biling.date_record BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
              AND biling.type = 'TERM'
              AND sim_owner.state = 1
              AND user.lft >= {$user->getLeft()}
              AND user.rgt <= {$user->getRight()}";
        $result = $em->getConnection()->query($sql)->fetchAll();
        $data = array();
        foreach ($result as $key => $value) {
            @$data[$value['label']] += $value['duration'];
        }
        $result = array();
        $i = 0;
        foreach ($data as $key => $value) {
            $result[$i]['label'] = $key;
            $result[$i]['data'] = $value;
            $result[$i]['color'] = randcolor();
            $i++;
        }
        echo json_encode($result);
        break;
    default :
}
exit();
//END KERCH


$map = getMap($type);

$sql = "select user.name as label, {$map['data']} as data, 'red' as color
            from user 
            inner join sim_owner on user.id = sim_owner.user_id
            inner join biling on biling.phone = sim_owner.sim_id
            where 
                biling.date between '$dateBegin 00:00:00' and '$dateEnd 23:59:59' and
                sim_owner.state = 1 and
                user.lft >= {$user->getLeft()} and
                user.rgt <= {$user->getRight()} and
                biling.type = '{$map['where']}'
            group by label";

//$sql="select user.name as label, SUM('amount') as data, '#80699B' as color from user inner join sim_owner on user.id = sim_owner.user_id inner join biling on biling.phone = sim_owner.sim_id where biling.date between '2012-05-21 00:00:00' and '2013-05-28 23:59:59' and sim_owner.state = 1 and user.lft > 1 and user.rgt < 56 and biling.type = 'FILLUP' group by label";

$result = $em->getConnection()->query($sql)->fetchAll();

//echo json_encode($sql);
//print_r($result);


$result2 = array();
foreach ($result as $key => $value) {
    $result2[$key]['label'] = $value['label'] = $value['label'];
    $result2[$key]['data'] = $value['data'] = (float)$value['data'];
    $result2[$key]['color'] = $value['color'] = randcolor();
}
//print_r($result2);
echo json_encode($result2);
exit();

function getMap($type)
{
    $map = array(
        //Оборот
        'amount' => array(
            'data' => 'SUM(`amount`)',
            'where' => 'FILLUP'
        ),
        //Число исходящих звонков
        'ORIG' => array(
            'data' => 'SUM(`duration`)',
            'where' => 'ORIG'
        ),
        //Число входящих звонков
        'TERM' => array(
            'data' => 'SUM(`duration`)',
            'where' => 'TERM'
        ),
        //Учет входящих минут
        'TERM_duration' => array(
            'data' => 'COUNT(`type`)',
            'where' => 'TERM'
        ),
        //Учет исходящих минут
        'ORIG_duration' => array(
            'data' => 'COUNT(`type`)',
            'where' => 'ORIG'
        ),
    );
    return $map[$type];
}

?>